from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta

from odoo import fields, models
from odoo.tools import DEFAULT_SERVER_DATE_FORMAT, DEFAULT_SERVER_DATETIME_FORMAT


class ProductionDailyLineReport(models.Model):
    _name = 's.production.daily.line.report'
    _description = "Production Daily Report Line"
    _auto = False

    production_id = fields.Many2one('mrp.production', string='Production')
    product_id = fields.Many2one('product.product', string='Product')
    workorder_id = fields.Many2one('mrp.workorder', 'Work order')
    qty = fields.Integer('Qty')
    done_date = fields.Datetime('Done Date')

    @property
    def _table_query(self):
        station_id = self._context.get('station_id')
        prod_date = self._context.get('prod_date')
        x_date = datetime.strptime(prod_date, DEFAULT_SERVER_DATE_FORMAT)
        x_date = fields.Datetime.to_datetime(x_date)
        start_date = x_date.replace(hour=1, minute=30)
        end_date = (start_date + timedelta(days=1)).replace(hour=1, minute=30)
        three_date = start_date - relativedelta(months=1)
        four_date = end_date + relativedelta(months=1)
        sql = f"""
            WITH step_sequence AS (SELECT mwo.id mwo_id,
                              mwo.production_id,
                              mwo.product_id,
                              mwc.s_work_station_id work_station_id,
                              mwo.step,
                              mwo.date_finished,
                              case
                                  when ws.code in ('FQC', 'FQA', 'PKG') then mwo.qty_op_produced_set
                                  else mwo.qty_op_produced_pnl end qty,
                              -- 批次检测
                              CASE
                                  WHEN mwc.work_station_id <> LAG(mwc.work_station_id)
                                                              OVER (PARTITION BY mwo.production_id ORDER BY mwo.step)
                                      OR LAG(mwo.production_id)
                                         OVER (PARTITION BY mwo.production_id ORDER BY mwo.step) IS NULL
                                      THEN 1
                                  ELSE 0
                                  END AS                           is_new_batch
                       FROM mrp_workorder mwo
                                JOIN mrp_workcenter mwc ON mwo.workcenter_id = mwc.id
                                left join work_station ws on mwc.s_work_station_id = ws.id
                                left join mrp_production mp on mp.id = mwo.production_id
                       WHERE mwo.state = 'done'
                         and mp.mo_type != 'W'
                         and mwc.s_not_compute_output != TRUE 
                         and mwo.qty_op_produced > 0
                         and mwo.date_finished >= '{three_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                         and mwo.date_finished < '{four_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                         order by mwo.production_id, mwo.step
                         ),
                     batch_groups AS (SELECT *,
                                             SUM(is_new_batch) OVER (
                                                 PARTITION BY production_id
                                                 ORDER BY step
                                                 ) AS batch_id
                                      FROM step_sequence),
                     batch_summary AS (SELECT work_station_id,
                                              production_id,
                                              batch_id,
                                              MAX(date_finished) AS batch_end_time,
                                              min(qty)           AS total_output,
                                              MAX(mwo_id) mwo_id
                                       FROM batch_groups
                                       GROUP BY work_station_id, batch_id, production_id)
                     -- 关键部分：自定义日期时段处理
                     SELECT row_number() OVER() AS id,
                             batch_summary.work_station_id,
                             batch_summary.production_id production_id,
                             batch_end_time done_date,
                             pp.id product_id,
                             total_output qty,
                             batch_summary.mwo_id workorder_id,
                             CASE WHEN ws.code in ('FQC', 'FQA', 'PKG') then total_output * setl.uom_value * setw.uom_value / 1000000
                                else total_output * pnll.uom_value * pnlw.uom_value / 1000000 end area,
                             -- 将时间调整到1点为分界点的日期
                             CASE
                                 -- 当时间在00:00:00到01:29:59之间时，归属前一天
                                 WHEN batch_end_time::time < '01:30:00'::time 
                                   THEN DATE(batch_end_time - INTERVAL '1 day')
                                 -- 当时间在01:30:00到23:59:59之间时，归属当天
                                 ELSE DATE(batch_end_time)
                               END AS production_date
                      FROM batch_summary
                      LEFT  JOIN work_station ws on ws.id = batch_summary.work_station_id
                      LEFT JOIN mrp_production mp on mp.id = batch_summary.production_id
                      LEFT JOIN product_product pp ON mp.product_id = pp.id
                        LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                        LEFT JOIN product_uom_value pnll ON pnll.product_id = pt.id AND pnll.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLL')
                        LEFT JOIN product_uom_value pnlw ON pnlw.product_id = pt.id AND pnlw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLW')
                        LEFT JOIN product_uom_value setl ON setl.product_id = pt.id AND setl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                        LEFT JOIN product_uom_value setw ON setw.product_id = pt.id AND setw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW') 
                      where batch_summary.batch_end_time >= '{start_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                        and batch_summary.batch_end_time < '{end_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                        and batch_summary.work_station_id = {station_id}
        """
        return sql
